<?php

class Dashboard extends Admin_Controller
{
	public function __construct()
	{
		parent::__construct();	
		$this->load->model('voucher/fund_model', 'fund');
	}
	
	public function view($fund_id)
	{
		$agency_id = login_data('agency_id');
		
		if($_GET['year'])
		{
			$year = ($_GET['year'] - 543);
		}
		else
		{
			$row = $this->db->getrow('SELECT STATUS_YEAR, STATUS_MONTH FROM STATUS WHERE FUND_ID = ? AND AGENCY_ID = ? ORDER BY STATUS_YEAR DESC, STATUS_MONTH DESC', array($fund_id, $agency_id));
			$year = $row ? Fund::convert_to_year_budget($row['STATUS_YEAR'], $row['STATUS_MONTH']) : Fund::convert_to_year_budget(date('Y'), date('m'));;
		}
		$data['year'] = $year + 543;

		$sql = "SELECT STATUS.*, AGENCIES.NAME AS AGENCY_NAME FROM STATUS 
		JOIN AGENCIES ON AGENCIES.ID = STATUS.AGENCY_ID 
		WHERE FUND_ID = $fund_id AND AGENCY_ID = $agency_id 
		AND ((STATUS_YEAR = ".($year - 1)." AND STATUS_MONTH > 9) OR (STATUS_YEAR = ".($year)." AND STATUS_MONTH < 10))
		ORDER BY STATUS_YEAR DESC, STATUS_MONTH DESC";
		$data['result'] = $this->fund->get($sql);
		$data['pagination'] = $this->fund->pagination();
		
		$this->template->build('view', $data);
	}	
	
	public function all($fund_id)
	{
		if($_GET)
		{
			$year = $_GET['year'] - 543;
			$month = $_GET['month'];
		}
		else
		{
			$year = $year ? $year : Fund::convert_to_year_budget(date('Y'), date('m'));
			$month = $month ? $month : date('m');
		}	
		
		$data['year'] = $year + 543;
		$data['month'] = $month;
		$data['fund'] = $this->fund->get_row($fund_id);
		
		//$year = Fund::convert_to_year_budget($year, $month);
		$year = Fund::convert_to_real_year($year, $month);

		$sql = 'SELECT A.NAME AS AGENCY_NAME, A.ID AS A_ID, S.*
		FROM AGENCIES AS A
		LEFT JOIN (SELECT * FROM STATUS WHERE STATUS.FUND_ID = ? AND STATUS_YEAR = ? AND STATUS_MONTH = ?) AS S ON S.AGENCY_ID = A.ID
		WHERE A.ID > 2 AND A.ID < 79
		ORDER BY AGENCY_NAME ASC';
		//$this->db->debug = true;
		$data['provinces'] = $this->db->getarray($sql, array($fund_id, $year, $data['month']));	 
		//$this->db->debug = FALSE;
		dbConvert($data['provinces']);
		
		// การบันทึกบัญชีใบสำคัญรับเงินส่วนของพมจ.
		$sql = 'SELECT REGISTER_TITLES.ID, REGISTER_TITLES.COL_TITLE, REGISTER_TITLES.SIDE, REGISTER_TITLES.SUB, DETAILS.COL, DETAILS.TOTAL_PRICE, REGISTER_TITLES.ACCOUNT_ID'.$fund_id.' AS ACCOUNT_ID 
		FROM REGISTER_TITLES
		JOIN (
			SELECT REGISTERS.TITLE_ID, REGISTER_DETAILS.COL, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = ?
			AND YEAR(REGISTERS.DATE_RECORD) = ?
			AND MONTH(REGISTERS.DATE_RECORD) = ?	
			AND REGISTER_DETAILS.IS_PRICE = 1
			GROUP BY REGISTER_DETAILS.COL, REGISTERS.TITLE_ID
			) AS DETAILS ON DETAILS.TITLE_ID = REGISTER_TITLES.ID
		WHERE REGISTER_TITLES.SUB IN (2, 3, 5)  
		ORDER BY REGISTER_TITLES.ID ASC';
		$data['vouchers'] = $this->db->getarray($sql, array($fund_id, $year, $data['month']));
		dbConvert($data['vouchers']);
		
		$data['voucher_total'] = $this->db->getassoc('SELECT REGISTERS.SIDE, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTERS.FUND_ID = ?
		AND YEAR(REGISTERS.DATE_RECORD) = ?
		AND MONTH(REGISTERS.DATE_RECORD) = ?	
		AND REGISTER_DETAILS.IS_PRICE = 1
		AND SUB IN (2,3,5)
		GROUP BY REGISTERS.SIDE', array($fund_id, $year, $data['month']));
		dbConvert($data['voucher_total']);
		
		// เรียกข้อมูลใบสำคัญมาแก้ไข
		$data['rs'] = NULL;
		$rs = $this->db->getrow('SELECT * 
		FROM VOUCHERS
		WHERE VOUCHERS.FUND_ID = ? 
		AND YEAR(VOUCHERS.VOUCHER_DATE) = ?
		AND MONTH(VOUCHERS.VOUCHER_DATE) = ?',
		array($fund_id, $year, $data['month']));
		if(!empty($rs))
		{
			dbConvert($rs);
			$data['rs'] = $rs;
		}
		
		$this->template->append_metadata(js_datepicker());
		$this->template->build('all', $data);
	}
	
	public function complete($fund_id, $year, $month)
	{
		//Fund::save_status($fund_id, login_data('agency_id'));
		
		//$this->load->model('voucher/status_model');
		//$this->db->debug = true;
		/*
		$status = $this->db->getrow('SELECT * FROM STATUS WHERE FUND_ID = ? AND AGENCY_ID = ? AND STATUS_YEAR = ? AND STATUS_MONTH = ?', 
		array($fund_id, login_data('agency_id'), $year, $month));
		dbConvert($status);
		echo 'ID:'.$status['id'];
		if($status['id'])
		{
			$data = array(
				'id' => $status['id'],
				'status' => 1
			);
			$this->status_model->save($data);	
		}
		*/
		$this->db->execute('UPDATE STATUS SET STATUS = 1 WHERE FUND_ID = ? AND AGENCY_ID = ? AND STATUS_YEAR = ? AND STATUS_MONTH = ?', array($fund_id, login_data('agency_id'), $year, $month));

		//**************Start lock agency ******************
		$data = $this->db->getRow('select * from agency_details where fund_id = ? and agency_id = ?',array($fund_id,login_data('agency_id')));
		dbConvert($data);
		if($data)
		{
			$data['is_lock'] = '1';
		}
		else
		{
			$data['fund_id'] = $fund_id;
			$data['agency_id'] = login_data('agency_id');
			$data['is_lock'] = '1';
		}
		$this->load->model('setting/agency_detail_model', 'agency_detail');
		$this->agency_detail->save($data);


		//**************End lock agency ******************
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	public function change_status($fund_id, $agency_id, $year, $month, $status)
	{	
		//->db->debug = true;
		$year = Fund::convert_to_real_year($year, $month);
		$year -= 543;
		$this->load->model('voucher/status_model', 'status');
		$id = $this->db->getone('SELECT ID FROM STATUS WHERE FUND_ID = ? AND AGENCY_ID = ? AND STATUS_YEAR = ? AND STATUS_MONTH = ?', array($fund_id, $agency_id, $year, $month));
		$data = array(
			'fund_id' => $fund_id,
			'agency_id' => $agency_id,
			'status_year' => $year,
			'status_month' => $month,
			'status' => $status
		);
		if($id) $data['id'] = $id;
		
		$status_id = $this->status->save($data);
		//$this->db->debug = FALSE;
		if($status_id and $status == 2)
		{
			$month += 1;
			if($month > 12)
			{
				$month = 1;
				$year += 1;
			}
			$status = array(
				'fund_id' => $fund_id,
				'agency_id' => $agency_id,
				'status_year' => $year,
				'status_month' => $month,
				'status' => 0
			);
			$this->status->save($status);
		}
		redirect($_SERVER['HTTP_REFERER']);
	}
}
